package com.eece496.Database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.eece496.Helpers.GroupInfo;
import com.eece496.Helpers.StudentGradeInfo;
import com.eece496.Helpers.StudentInfo;

public class StudentGroups extends Database{

	private String groupNumber;
	private String division;
	private int studentID;
	private String courseNumber;
	private String sectionNumber;
	private String classroom;
	private int priority;
	
	public StudentGroups() {
		tableName = "StudentGroups";
	}

	public StudentGroups(String groupNumber, String division, int studentID, String courseNumber, String sectionNumber, String classroom, int priority) {
		tableName = "StudentGroups";

		this.groupNumber = groupNumber;
		this.division = division;
		this.studentID = studentID;
		this.courseNumber = courseNumber;
		this.sectionNumber = sectionNumber;
		this.classroom = classroom;
		this.priority = priority;
	}
	
	@Override
	public void Insert() throws SQLException {
		Statement stmt = connection.createStatement();
		
	    String sqlString = "INSERT INTO " + tableName + " VALUES (" + 
						AddQuotes(groupNumber) + ", " +
						AddQuotes(division) + ", " +
	    					studentID + ", " +
	    					AddQuotes(courseNumber) + ", " +
	    					AddQuotes(sectionNumber) +  ", " +
	    					AddQuotes(classroom) + ", " +
	    					priority +
	    					")";
	    
	    stmt.executeUpdate(sqlString);
	        
	    System.out.println(sqlString + " complete");
		
	}

	@Override
	public void Delete(String primaryKey) throws SQLException {
		// TODO Auto-generated method stub
		
	}
	
	public void DeleteCourseGroups(String courseNumber, String sectionNumber) throws SQLException
	{
		Statement stmt = connection.createStatement();
		String sqlString = "DELETE from " + tableName + " WHERE courseNumber = " + AddQuotes(courseNumber) +
				" and sectionNumber= " + AddQuotes(sectionNumber);
		stmt.executeUpdate(sqlString);
	}
	
	public void DeleteStudentFromGroup(int studentID, String courseNumber, String sectionNumber) throws Exception
	{
		Statement stmt = connection.createStatement();
		
		String sqlString = "DELETE FROM " + tableName + " WHERE " +
		" studentID = " + studentID + " and courseNumber= " + AddQuotes(courseNumber) + " and sectionNumber = " + AddQuotes(sectionNumber);
		
		stmt.executeUpdate(sqlString);
	}

	@Override
	public void Display() throws SQLException {
		// TODO Auto-generated method stub
		
	}
	
	public int getNumberOfGroupsInDivision(String courseNumber, String sectionNumber, String division) throws SQLException
	{
		int count = 0;
		
		Statement stmt = connection.createStatement();
		
		String sqlString = "select groupNumber from " + tableName + " WHERE " +
				" division = " + AddQuotes(division) + 
				" and courseNumber= " + AddQuotes(courseNumber) + " and sectionNumber = " + AddQuotes(sectionNumber) + " group by GroupNumber";
		
		ResultSet rs = stmt.executeQuery(sqlString);

		while(rs.next())
		{
			count++;
		}
		
		return count;
	}
	
	public int GetPriority(int studentID, String courseNumber, String sectionNumber) throws SQLException{
		int priority = 0;
		
		Statement stmt = connection.createStatement();
		
		String sqlString = "select priority from " + tableName + " WHERE " +
				" studentID = " + studentID + 
				" and courseNumber= " + AddQuotes(courseNumber) + " and sectionNumber = " + AddQuotes(sectionNumber);
		
		ResultSet rs = stmt.executeQuery(sqlString);
		
		
		while(rs.next())
		{
			priority = rs.getInt("priority");
		}
		return priority;
	}
	
	public void ModifyPriority(int priority, int studentID, String courseNumber, String sectionNumber) throws SQLException
	{
		Statement stmt = connection.createStatement();
		String sqlString = "UPDATE " + tableName + " SET " + "priority" + " = " + priority + " WHERE courseNumber = " + AddQuotes(courseNumber)
				+ " and sectionNumber= " + AddQuotes(sectionNumber) + " and studentID = " + studentID;
		stmt.executeUpdate(sqlString);
		System.out.println(sqlString + " complete");	
	}
	
	public int GetGroupSize(String groupNumber, String division, String courseNumber, String sectionNumber) throws SQLException
	{
		Statement stmt = connection.createStatement();
		
		String sqlString = "select * from " + tableName + " WHERE " +
				" groupNumber = " + AddQuotes(groupNumber) + " and division = " + AddQuotes(division) + 
				" and courseNumber= " + AddQuotes(courseNumber) + " and sectionNumber = " + AddQuotes(sectionNumber);
		
		ResultSet rs = stmt.executeQuery(sqlString);
		
		int count = 0;
		
		while(rs.next())
		{
			count++;
		}
		return count;
	}
	
	public ArrayList<GroupInfo> GetAllGroups(String courseNumber, String sectionNumber) throws SQLException
	{
		ArrayList<GroupInfo> list = new ArrayList<GroupInfo>();
		
		Statement stmt = connection.createStatement();

		String sqlString = "select StudentGroups.groupNumber, StudentGroups.division, StudentGroups.studentID, StudentGroups.classroom, Student.firstname, " +
				"Student.program, Student.theYear, " + 
				"Student.lastname from " +
				" StudentGroups inner join Student on StudentGroups.studentID = Student.studentID " +
				"where StudentGroups.courseNumber = " + AddQuotes(courseNumber) + " and StudentGroups.sectionNumber = " +
				AddQuotes(sectionNumber) + " order by division ASC, groupNumber ASC";

		ResultSet rs = stmt.executeQuery(sqlString);

		while (rs.next()) {
			GroupInfo info = new GroupInfo();

			info.division = rs.getString("division");
			info.groupNumber = rs.getString("groupNumber");
			info.studentID = rs.getInt("studentID");
			info.firstName = rs.getString("firstName");
			info.lastName = rs.getString("lastName");
			info.program = rs.getString("program");
			info.year = rs.getInt("theyear");
			info.classroom = rs.getString("classroom");
			list.add(info);
		}
		return list;
	}
	
	public ArrayList<GroupInfo> GetUnGroupedStudents(String courseNumber, String sectionNumber) throws SQLException
	{
		ArrayList<GroupInfo> list = new ArrayList<GroupInfo>();
		
		Statement stmt = connection.createStatement();
		String sqlString = "select Enrollment.studentID, Student.firstname, Student.program, Student.theYear, " + 
				"Student.lastname from " +
				" Enrollment inner join Student on Enrollment.studentID = Student.studentID " +
				"where Enrollment.courseNumber = " + AddQuotes(courseNumber) + " and Enrollment.sectionNumber = " +
				AddQuotes(sectionNumber);
		
		ResultSet rs = stmt.executeQuery(sqlString);
		
		while(rs.next())
		{
			GroupInfo info = new GroupInfo();
			
			info.studentID = rs.getInt("studentID");
			info.firstName = rs.getString("firstName");
			info.lastName = rs.getString("lastName");
			info.program = rs.getString("program");
			info.year = rs.getInt("theYear");
			list.add(info);
		}
		
		System.out.println(list.size());
		return list;
	}
	
	public boolean UserExists(String courseNumber, String sectionNumber, int studentID) throws SQLException
	{
		ArrayList<GroupInfo> list = new ArrayList<GroupInfo>();
		Statement stmt = connection.createStatement();
		String sqlString = "select * from studentgroups "
				+ " where courseNumber = " + AddQuotes(courseNumber) + " and "
				+ " sectionNumber = " + AddQuotes(sectionNumber) + " and studentID = " + studentID;
			
		ResultSet rs = stmt.executeQuery(sqlString);
		if(rs.next())
		{
			//System.out.println("true");
			return true;
		
		}
		else{
			//System.out.println("false");
		return false;

		}
	}
	
	
	public ArrayList<GroupInfo> GetStudentGroupInfo(String courseNumber, String sectionNumber, int groupNumber, String division) throws SQLException
	{
		ArrayList<GroupInfo> list = new ArrayList<GroupInfo>();
		
		Statement stmt = connection.createStatement();

		String sqlString = "select Student.firstname, " + 
				"Student.lastname from " +
				" StudentGroups inner join Student on StudentGroups.studentID = Student.studentID " +
				"where StudentGroups.courseNumber = " + AddQuotes(courseNumber) + " and StudentGroups.sectionNumber = " +
				AddQuotes(sectionNumber) + " and groupNumber = " + groupNumber + " and division = " + AddQuotes(division);

		ResultSet rs = stmt.executeQuery(sqlString);

		while (rs.next()) {
			GroupInfo info = new GroupInfo();
			info.firstName = rs.getString("firstName");
			info.lastName = rs.getString("lastName");
			list.add(info);
		}
		return list;
	}
	
	public ArrayList<GroupInfo> GetTwoStudentGroupInfo(String courseNumber, String sectionNumber, int groupNumber, int groupNumberNext, String division) throws SQLException
	{
		ArrayList<GroupInfo> list = new ArrayList<GroupInfo>();
		
		Statement stmt = connection.createStatement();

		String sqlString = "select Student.firstname, " + 
				"Student.lastname, Student.studentID from " +
				" StudentGroups inner join Student on StudentGroups.studentID = Student.studentID " +
				"where StudentGroups.courseNumber = " + AddQuotes(courseNumber) + " and StudentGroups.sectionNumber = " +
				AddQuotes(sectionNumber) + " and groupNumber = " + groupNumber + " and division = " + AddQuotes(division);

		ResultSet rs = stmt.executeQuery(sqlString);

		while (rs.next()) {
			GroupInfo info = new GroupInfo();
			info.firstName = rs.getString("firstName");
			info.lastName = rs.getString("lastName");
			info.studentID = rs.getInt("studentID");
			list.add(info);
		}
		
		Statement stmt1 = connection.createStatement();

		String sqlString1 = "select Student.firstname, " + 
				"Student.lastname, Student.studentID from " +
				" StudentGroups inner join Student on StudentGroups.studentID = Student.studentID " +
				"where StudentGroups.courseNumber = " + AddQuotes(courseNumber) + " and StudentGroups.sectionNumber = " +
				AddQuotes(sectionNumber) + " and groupNumber = " + groupNumberNext + " and division = " + AddQuotes(division);

		ResultSet rs1 = stmt1.executeQuery(sqlString1);

		while (rs1.next()) {
			GroupInfo info = new GroupInfo();
			info.firstName = rs1.getString("firstName");
			info.lastName = rs1.getString("lastName");
			info.studentID = rs1.getInt("studentID");
			list.add(info);
		}
		
		System.out.println("Number of Students in the Group: "+list.size());
		
		return list;
	}
	
	
	public ArrayList<Integer> GetGroupNumbers(String courseNumber, String sectionNumber, String division) throws SQLException
	{
		ArrayList<Integer> list = new ArrayList<Integer>();
		
		Statement stmt = connection.createStatement();

		String sqlString = "select groupNumber from " +
				" StudentGroups " +
				"where courseNumber = " + AddQuotes(courseNumber) + " and sectionNumber = " +
				AddQuotes(sectionNumber) + " and division = " + AddQuotes(division) + " group by groupNumber";

		ResultSet rs = stmt.executeQuery(sqlString);
		while(rs.next()){
			Integer i;
			i = rs.getInt("groupNumber");
			list.add(i);
		}
		
		return list;
	}
	
	public int  GetGroupNumberFromStudent(int studentId, String courseNumber, String sectionNumber) throws SQLException
	{
		//ArrayList<Integer> list = new ArrayList<Integer>();
		int groupNumber = 0;
		Statement stmt = connection.createStatement();

		String sqlString = "select groupNumber from " +
				" StudentGroups " +
				"where courseNumber = " + AddQuotes(courseNumber) + " and sectionNumber = " +
				AddQuotes(sectionNumber) + " and studentid= " + studentId;

		ResultSet rs = stmt.executeQuery(sqlString);
		while(rs.next()){
			//Integer i;
			//i = rs.getInt("groupNumber");
			//list.add(i);
			groupNumber = rs.getInt("groupNumber");
		}
		
		return groupNumber;
	}

	public String GetDivisionFromStudent(int studentId, String courseNumber, String sectionNumber) throws SQLException
	{
		String division = null;
		Statement stmt = connection.createStatement();

		String sqlString = "select division from " +
				" StudentGroups " +
				"where courseNumber = " + AddQuotes(courseNumber) + " and sectionNumber = " +
				AddQuotes(sectionNumber) + " and studentid= " + studentId;

		ResultSet rs = stmt.executeQuery(sqlString);
		while(rs.next()){
			//Integer i;
			//i = rs.getInt("groupNumber");
			//list.add(i);
			division = rs.getString("division");
		}
		
		return division;
	}
	
	public ArrayList<Integer> GetStudentsFromGroup(int groupNumber, String courseNumber, String sectionNumber, String division) throws SQLException
	{
		ArrayList<Integer> list = new ArrayList<Integer>();
		
		Statement stmt = connection.createStatement();

		String sqlString = "select studentID from " +
				" StudentGroups " +
				" where courseNumber = " + AddQuotes(courseNumber) + " and sectionNumber = " +
				AddQuotes(sectionNumber) + " and groupNumber = " + groupNumber + " and division = " + AddQuotes(division) + "order by priority ASC";

		ResultSet rs = stmt.executeQuery(sqlString);
		while(rs.next()){
			Integer i;
			i = rs.getInt("studentID");
			list.add(i);
		}
		
		return list;
	}
	
	public ArrayList<String> GetDivisions(String courseNumber, String sectionNumber) throws SQLException
	{
		ArrayList<String> list = new ArrayList<String>();
		
		Statement stmt = connection.createStatement();
		
		String sqlString = " select division from " + tableName + " where courseNumber = " + AddQuotes(courseNumber) +
				" and sectionNumber = " + AddQuotes(sectionNumber) + " group by division";
		
		ResultSet rs = stmt.executeQuery(sqlString);
		
		while(rs.next()){
			list.add(rs.getString("division"));
		}
		
		return list;
	}
}